package com.css.zfzx.sjcj.modules.qhsiteclassresult.repository;

import com.css.bpm.platform.api.local.PlatformAPI;
import com.css.bpm.platform.org.role.repository.entity.RoleEntity;
import com.css.bpm.platform.utils.PlatformDateUtils;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.sjcj.common.utils.ProjectTransUtil;
import com.css.zfzx.sjcj.common.utils.ServerUtil;
import com.css.zfzx.sjcj.common.utils.VectorDataUtil;
import com.css.zfzx.sjcj.modules.qhsiteclassresult.repository.entity.QhSiteClassResultEntity;
import com.css.zfzx.sjcj.modules.qhsiteclassresult.viewobjects.QhSiteClassResultQueryParams;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.opengis.feature.simple.SimpleFeature;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 * @Author leon
 * @Date: 2021/2/2 10:24 上午
 */
@Repository
@PropertySource("application-dev.yml")
public class QhSiteClassResultImport {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";
    private static Connection conn;
    private static PreparedStatement ps;


    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchEntities(List<QhSiteClassResultEntity> list) throws Exception {
        int count = 0;

        conn = getConnection();

        List<QhSiteClassResultEntity> subList = null;
        int total = list.size(), num = 5000, time = total / num + 1, from = 0, to = 0;
        for (int i = 0; i < time; i++) {
            from = i * num;
            to = (i == time - 1) ? total : (i + 1) * num;
            subList = list.subList(from, to);
            if(subList!=null && subList.size()>0) {
                StringBuffer sql = new StringBuffer("insert into qh_site_class_result (uuid, province, city, area, affiliated_file_name, usa_classi, shape_leng, shape_le_1, shape_area, coordinates, object_code, partition_flag, create_user, create_time, extend1) "
                        + "values ");
                // 上万条数据
                for (QhSiteClassResultEntity entity : subList) {
                    count++;
                    String id = UUIDGenerator.getUUID();
                    String province = entity.getProvince();
                    String city = entity.getCity();
                    String area = entity.getArea();
                    String affiliatedFileName = entity.getAffiliatedFileName();
                    String usa_classi = entity.getUsaClassi();
                    Double shape_leng = entity.getShapeLeng();
                    shape_leng = (shape_leng != null ? shape_leng.doubleValue() : 0);
                    Double shape_le_1 = entity.getShapeLe1();
                    shape_le_1 = (shape_le_1 != null ? shape_le_1.doubleValue() : 0);
                    Double shape_area = entity.getShapeArea();
                    shape_area = (shape_area != null ? shape_area.doubleValue() : 0);
                    String coordinates = entity.getCoordinates();

                    String objectCode = entity.getObjectCode();
                    objectCode = (objectCode != null ? objectCode : "");
                    String partitionFlag = entity.getPartitionFlag();
                    partitionFlag = (partitionFlag != null ? partitionFlag : "");
                    String createUser = PlatformSessionUtils.getUserId();
                    createUser = (createUser != null ? createUser : "");
                    Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();

                    String extend1 = entity.getExtend1();
                    extend1 = (extend1 != null ? extend1 : "");

                    sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName +
                            "','" + usa_classi + "','" + shape_leng + "','" + shape_le_1 + "','" + shape_area + "','" + coordinates +
                            "','" + objectCode + "','" + partitionFlag + "','" + createUser + "','" + createTime + "', '" + extend1 + "'),");
                }
                String sql_str = sql.toString();
                sql_str = sql_str.substring(0, sql_str.length() - 1);
                ps = conn.prepareStatement(sql_str);
                ps.executeUpdate(sql_str);
                if (ps != null) ps.close();
            }
        }
        if (conn != null) conn.close();
        return count;
    }

    public int sqlBatchDeleteEntities(List<QhSiteClassResultEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("DELETE FROM qh_site_class_result WHERE uuid IN ( ");
            // 上万条数据
            for (QhSiteClassResultEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    public int sqlBatchLogicDeleteEntities(List<QhSiteClassResultEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("UPDATE qh_site_class_result set is_valid = '0',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
            // 上万条数据
            for (QhSiteClassResultEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    //保存--zyt
    public int newSqlBatchSaveEntities(String filePath, String province, String city, String area, String codeType, int dataLength, String createUser) {
        int count = 0;
        Connection conn = null;
        try {
            conn = getConnection();
            //zyt--获取省级表名
            String tableName = VectorDataUtil.getSpecialTableNameProvince(province, "", "", "SCR", true);
            //test
            //tableName = "qh_site_class_result_city2";

            String fileName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.length() - 4);
            String prjPath = filePath.substring(0, filePath.length() - 4) + ".prj";
            String strWKTMercator = VectorDataUtil.readPRJ(prjPath);
            if (strWKTMercator != "") ProjectTransUtil.setStrWKTMercator(strWKTMercator);
            System.out.println(new Date()+"-------------------1.开始readSHPFile");
            SimpleFeatureCollection colls = VectorDataUtil.readShp(filePath, codeType);
            System.out.println(new Date()+"-------------------2.结束readSHPFile");
            String tempsql = "insert into " + tableName + " (uuid, province, city, area, affiliated_file_name, usa_classi, shape_leng, shape_le_1, shape_area, coordinates, object_code, is_valid, partition_flag, create_user, create_time, extend1) values ";
            StringBuffer sql = new StringBuffer("");
            //int total = dataLength;
            //int num = 5000;
            int num = 100;
            boolean bool = false;//是否保存
            //开始时间
            Long begin = new Date().getTime();
            //zyt-test
            int size = 0;
            boolean tempBool = true;//判断是否需要转换经纬度值
            String lonLatStr = "", wkt = "";
            SimpleFeatureIterator iters = colls.features();
            while (iters.hasNext()) {
                count++;
                bool = false;//是否保存
                if(count % num == 0){
                    bool = true;//是否保存
                    //开始时间
                    begin = new Date().getTime();
                    System.out.println(count);
                }
                SimpleFeature sf = iters.next();
                List<Object> values = sf.getAttributes();
                size = values.size();
                /*if(tempBool){
                    wkt = values.get(0).toString();
                    if (wkt.indexOf("MULTIPOINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("MULTIPOLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    }
                    values.set(0, lonLatStr);
                    //判断是否经纬度值是否相等
                    if(wkt.equals(lonLatStr)){
                        tempBool = false;//判断是否需要转换经纬度值
                    }
                }*/
                String id = UUIDGenerator.getUUID();
                String affiliatedFileName = fileName;
                String usa_classi = values.get(1).toString();
                usa_classi = VectorDataUtil.transUsaClassi(usa_classi);
                Double shape_leng = null;
                if (size >= 3) shape_leng = Double.parseDouble(values.get(2).toString());
                shape_leng = (shape_leng != null ? shape_leng.doubleValue() : 0);
                Double shape_le_1 = null;
                if (size >= 4) shape_le_1 = Double.parseDouble(values.get(3).toString());
                shape_le_1 = (shape_le_1 != null ? shape_le_1.doubleValue() : 0);
                Double shape_area = null;
                if (size >= 5) shape_area = Double.parseDouble(values.get(4).toString());
                shape_area = (shape_area != null ? shape_area.doubleValue() : 0);
                String coordinates = values.get(0).toString();

                String objectCode = "";
                String is_valid = "1";
                String partitionFlag = "";
                //String createUser = PlatformSessionUtils.getUserId();
                createUser = (createUser != null ? createUser : "");
                Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();

                String extend1 = "CGCS2000";

                sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName +
                        "','" + usa_classi + "','" + shape_leng + "','" + shape_le_1 + "','" + shape_area + "','" + coordinates +
                        "','" + objectCode + "','" + is_valid + "','" + partitionFlag + "','" + createUser + "','" + createTime + "', '" + extend1 + "'),");

                if(bool){
                    String sql_str = tempsql + sql.toString();
                    sql_str = sql_str.substring(0, sql_str.length() - 1);
                    PreparedStatement ps = conn.prepareStatement(sql_str);
                    ps.executeUpdate(sql_str);
                    if (ps != null) ps.close();
                    //结束时间
                    Long end = new Date().getTime();
                    //耗时
                    //System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                    //清空
                    sql.setLength(0);
                }
            }
            if(!bool){
                //开始时间
                //begin = new Date().getTime();
                String sql_str = tempsql + sql.toString();
                sql_str = sql_str.substring(0, sql_str.length() - 1);
                PreparedStatement ps = conn.prepareStatement(sql_str);
                ps.executeUpdate(sql_str);
                if (ps != null) ps.close();
                //结束时间
                Long end = new Date().getTime();
                //耗时
                //System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                //清空
                sql.setLength(0);
            }
            iters.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("保存第" + count + "条数据出现异常!");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return count;
    }

    //删除--zyt
    /**
     * sql 批量逻辑删除
     *
     * @return
     */
    public int newSqlBatchLogicDeleteEntities(List<String> list) {
        int count = 0;
        Connection conn = null;
        try {
            conn = getConnection();

            List<String> subList = null;
            int total = list.size(), num = 5000, time = total / num + 1, from = 0, to = 0;
            for (int i = 0; i < time; i++) {
                //开始时间
                Long begin = new Date().getTime();
                from = i * num;
                to = (i == time - 1) ? total : (i + 1) * num;
                subList = list.subList(from, to);
                if(subList!=null && subList.size()>0) {
                    StringBuffer sql = new StringBuffer("UPDATE qh_site_class_result set is_valid = '0',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");

                    // 上万条数据
                    for (String uuid : subList) {
                        count++;
                        String id = uuid;
                        sql.append("'" + id + "',");
                    }
                    String sql_str = sql.toString();
                    sql_str = sql_str.substring(0, sql_str.length() - 1) + " )";
                    PreparedStatement ps = conn.prepareStatement(sql_str);
                    ps.executeUpdate(sql_str);
                    if (ps != null) ps.close();
                    //结束时间
                    Long end = new Date().getTime();
                    //耗时
                    System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return count;
    }

    //zyt--查询数据的UUID列表
    public List<String> listQhSiteClassResultsUuids(QhSiteClassResultQueryParams queryParams, String superCode, String provinceCode, String cityCode, String areaCode) {

        List<String> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            StringBuilder sql = new StringBuilder("select uuid from qh_site_class_result ");
            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            } else {
                whereSql.append(" and (province IS NULL OR LENGTH(province)=0) ");
            }
            /*if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            } else {
                whereSql.append(" and (city IS NULL OR LENGTH(city)=0) ");
            }
            if (!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            } else {
                whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
            }*/
            //修改区县一级无法查询bug--添加查询文件名
            if(!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
                whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
            }
            whereSql.append(" and is_valid = '1' ");

            String userId = PlatformSessionUtils.getUserId(), isHaveAuthorityControl = queryParams.getIsHaveAuthorityControl();

            List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
            // 是否为超级管理员
            boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
            // 是否为省级管理员
            boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
            // 是否为市级管理员
            boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
            // 是否为区级管理员
            boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);

            if ("1".equals(isHaveAuthorityControl) && !containtRole) {
                // 如果都不是只能查看个人表单
                if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                    whereSql.append(" and create_user ='" + userId + "'");
                }
                String divisionName = ServerUtil.getDivisionEntity().getDivisionName();
                // 省级管理员
                if (containtRolePro) {
                    whereSql.append(" and province like '%" + divisionName + "%'");
                }
                // 市级管理员
                if (containtRoleCity) {
                    whereSql.append(" and city like '%" + divisionName + "%'");
                }
                // 区级管理员
                if (containtRoleArea) {
                    whereSql.append(" and area like '%" + divisionName + "%'");
                }
            }

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                String uuid = rs.getString("uuid");
                result.add(uuid);
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return result;
    }
}
